package DBOpt;

import java.util.*;
import java.util.Date;
import java.io.*;

import org.w3c.dom.*;
import org.xml.sax.SAXException;

import javax.xml.parsers.*;
import java.sql.*;
import java.util.List;
import twitter4j.Status;
import twitter4j.Twitter;
import java.text.SimpleDateFormat;

//import com.mysql.jdbc.*;

public class InsertApp {
 //
 Connection con;
 DocumentBuilderFactory factory;
 
  SimpleDateFormat createAtDateFormat;
  SimpleDateFormat outputTimestampFormat;
  SimpleDateFormat outputDateFormat,outputTimeFormat;
  
  protected  InsertApp(){
	//convert datetime
	// Format: Mon Sep 01 04:00:53 +0000 2008
	 createAtDateFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy",Locale.US);
	 outputTimestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.US);
	 outputDateFormat = new SimpleDateFormat("yyyy-MM-dd",Locale.US);
	 outputTimeFormat = new SimpleDateFormat("HH:mm:ss",Locale.US);
	 
 }
	
  public boolean Connect(String host, String user, String password,String dbname)
  {
	  factory = DocumentBuilderFactory.newInstance();  
	  try {
	      Class.forName("com.mysql.jdbc.Driver").newInstance();
	      
	  }
	  
	    catch( Exception e ) {
	      System.out.println("Failed to load mySQL driver.");
	      e.printStackTrace();
	      return false;
	    }
	    try {
	    //String url = "jdbc:mysql://"+host.trim()+"/"+dbname.trim();
	    	
	    //jdbc:mysql://hostname:port/database?autoReconnect=true&useUnicode=true&characterEncoding=utf8

	    String url = "jdbc:mysql://"+host.trim()+"/"+dbname.trim()+
	               "?autoReconnect=true&useUnicode=true&characterEncoding=utf8";
	      con = DriverManager.getConnection(url, user, password);
	      //con = DriverManager.getConnection(url);
	      System.out.println("Connect Succ!");
	      return true;
	    }
	    catch( Exception e ) {
	      e.printStackTrace();
	      return false;
	    }
	  }
  
  public void insertAllFile(String rootDirName, String tablename)
  {
	  
      File rootDir = new File(rootDirName);
      Calendar cal = Calendar.getInstance();
      FileWriter  finishedDirFile;
      int numOfDuplicate = 0;
      
      if (!rootDir.isDirectory()) 
      {
          // Either dir does not exist or is not a directory
    	  System.out.println(rootDirName+"is not a valid directory!");
    	  return;
      } 
    
      
      else 
      {
    	  String[] dir_children = rootDir.list();
    	  Arrays.sort(dir_children);

          // Use the factory to create a builder
    	  
    	  DocumentBuilder builder;
    	  try
    	  {
             builder = factory.newDocumentBuilder();
    	  }
    	  catch( Exception e ) 
  	      {
  	         e.printStackTrace();
  	         return;
  	       }
    	  
    	  //create error file to record duplicate file
    	  FileWriter errorFile;
    	  try
    	  {
    	   
    	   errorFile = new FileWriter ("..//output/meas/DuplicateRecord_"+tablename+".txt");
    	  // errorFile.write ("**** Started *****\n");
    	   System.out.println("create DuplicateRecord file");
    	  }
    	  catch( Exception e ) 
  	      {
  	         e.printStackTrace();
  	         return;
  	       }
          
    	  //read the last finished date in finished list
    	  int index = 0;
    	  try
    	  {
    		  FileInputStream in = new FileInputStream("./.finished_"+tablename+"_list");
        	  BufferedReader br = new BufferedReader(new InputStreamReader(in));
        	  		
        	  String strLine = null, tmp;

        	  while ((tmp = br.readLine()) != null)
        	  {
        	     strLine = tmp;
        	  }
        	  String lastLine = strLine;
        	  System.out.println("Previous Time:"+lastLine);
        	  if (lastLine != null){
        		  for(;index<dir_children.length;index++)
            	  {
            		String date = dir_children[index];
            		if (date.compareTo(lastLine)>0) break;
            		
            	  }
            	  if (index == dir_children.length)
            	  {
            		System.out.println ("No new data in incoming dir!");
            		return;
            	  }  
          		
        	  }
	 
    	  }
    	  catch (FileNotFoundException e)
    	  {
    		  System.out.println ("No previous finished dir");
    	  }
    	  catch( Exception e ) 
  	      {
  	         e.printStackTrace();
  	         return;
  	       }
    	  
    	  try
    	  {
    	   
    	   finishedDirFile = new FileWriter ("./.finished_"+tablename+"_list", true);
    	   System.out.println("creart finishfile");
    	  }
    	  catch( Exception e ) 
  	      {
  	         e.printStackTrace();
  	         return;
  	       }
    	  
    	  System.out.println ("*** Start processing from:"+dir_children[index]+" ****");
      	  
    	  for (; index<dir_children.length; index++) 
          {
              String subDirName = rootDirName+"/"+dir_children[index];
              File subDir = new File(subDirName);
              if (!subDir.isDirectory())
              {
            	  System.out.println(subDir+" is not a sub directory! ignore it!");  
              }
              else
              {
            	  String[] file_children = subDir.list();
            	  Arrays.sort(file_children);
            	  for (int j=0; j<file_children.length; j++) 
                  {
            		  
           		      String s= file_children[j];
            		  int surfix_index =  s.lastIndexOf("xml");
            		  if (surfix_index != file_children[j].length()-3 ) 
            	      {
            	    	  //System.out.println("Non-XML File:"+file_children[j]);
            	    	
            	      }
            		  
            	      else
            	      {
            	    	  numOfDuplicate = 0;
            	    	  String filename = subDirName+"/"+file_children[j];
                          File file = new File(filename);
                          System.out.println("XML File:"+filename);
                          if (file.length()<=0)
                          {
                        	  try{
                   	    	    errorFile.write(filename+"\t"+"Empty XML file"+"\n");
                              	errorFile.flush();
                              	continue;
                                }
                      	     catch( Exception e1 ) 
                    	      {
                    	         e1.printStackTrace();
                    	       }
                          }
                          
                          //Document doc = builder.parse(new java.io.ByteArrayInputStream(file.trim().getBytes("UTF8")));
                          List<Status>  statusList = null;
                          try{
                          Document doc = builder.parse(file);
                          // Get a list of all elements in the document
                          Twitter twitter = new Twitter();
                          statusList = Status.constructStatuses(doc, twitter);
                          }
                          catch( SAXException se ) 
                 	      {
                 	        System.out.println("XML Parse error:"+se.getMessage());
                 	       try{ 
                 	    	    errorFile.write(filename+"\t"+"XML Error:"+se.getMessage()+"\n");
                            	errorFile.flush();
                        	   
                              }
                    	     catch( Exception e1 ) 
                  	      {
                  	         e1.printStackTrace();
                  	       }
                 	        continue;
                 	      }
                           catch( Exception e ) 
                 	      {
                        	   try{
                    	        	errorFile.write(filename+"\t"+"UNKNOW Error:"+e.getMessage()+"\n");
                                 	errorFile.flush();
                             	   
                                   }
                         	     catch( Exception e1 ) 
                       	      {
                       	         e1.printStackTrace();
                       	       }
                        	 e.printStackTrace();
                 	        continue;
                 	       }
                          
                          for (Status status : statusList) {
                        	 String SQL="";
                        	 String userName, userScreenName, userLocation,userDescription, userProfileImageURL,userURL,text;
                        	 String source,createDate="",createTime="";
                            int id=0,isTruncated ,InReplyToStatusId, getInReplyToUserId,isFavorited,userId=0;
                          	  
                        	try{
                        	
                          	String CreatedAt = status.getCreatedAt();
                          	id = status.getId();
                          	text = status.getText();
                          	source = status.getSource();
                          	isTruncated =0;
                          	if (status.isTruncated()) isTruncated=1; 
                          	 InReplyToStatusId = status.getInReplyToStatusId();
                          	getInReplyToUserId = status.getInReplyToUserId() ;
                          	isFavorited = 0;
                          	if (status.isFavorited()) isFavorited =1;
                          	
                          	userId = status.getUser().getId();
                          	userName = status.getUser().getName();
                          	 userScreenName = status.getUser().getScreenName();
                          	 userLocation = status.getUser().getLocation();
                          	 userDescription = status.getUser().getDescription();
                          	 userProfileImageURL = status.getUser().getStrProfileImageURL();
                          	 userURL = status.getUser().getStrURL();
                          	int userFollowersCount = status.getUser().getFollowersCount();
                          	
                          	//convert date format
                                                   	                          	
                             Date creatDate = createAtDateFormat.parse(CreatedAt);
                             createDate = outputDateFormat.format(creatDate);
                             createTime = outputTimeFormat.format(creatDate);

                       	    String currentTime = outputTimestampFormat.format(cal.getTime());
                        	
                          	SQL ="insert into "+tablename+" values ("+
                          		'"'+currentTime+'"'+','+
                          		'"'+createDate+'"'+','+
                          		'"'+createTime+'"'+','+
                          		id+','+
                          		userId+','+
                          		'"'+replaceInvalidChar(userName)+'"'+','+
                          		'"'+replaceInvalidChar(text)+'"'+','+
                          		'"'+replaceInvalidChar(source)+'"'+','+
                          		isTruncated+','+
                          		InReplyToStatusId+','+
                          		getInReplyToUserId+','+
                          		isFavorited+','+
                          		
                          		'"'+replaceInvalidChar(userScreenName)+'"'+','+
                          		'"'+replaceInvalidChar(userLocation)+'"'+','+
                          		'"'+replaceInvalidChar(userDescription)+'"'+','+
                          		'"'+userProfileImageURL+'"'+','+
                          		'"'+userURL+'"'+','+
                          		userFollowersCount+
	                     		        ')';         
                          
                          }
                          catch( Exception e ) 
                 	      {
                        	  try{
                   	        	errorFile.write(filename+"\t"+"Twitter Error:"+e.getMessage()+"\n");
                                	errorFile.flush();
                            	   
                                  }
                        	     catch( Exception e1 ) 
                      	      {
                      	         e1.printStackTrace();
                      	       }
                        	  e.printStackTrace();
                 	       }
                          	System.out.println("Execute SQL: "+SQL);
                  		  try
                		  {
     
                            Statement insert_st = con.createStatement();
                             int result = insert_st.executeUpdate(SQL);
                             
                             if (result >0)
                             {
                          	    

                            	System.out.println ("Insert Successfully!");
                             }
                          }
                          catch( SQLException se )
                          {
                        	  if (se.getErrorCode() == 1062)
                             	{
                             	//record existed, update time stamp
                        		  numOfDuplicate ++;
                       		   System.out.println("Duplicate record. update time stamp");
                          	    String currentTime = outputTimestampFormat.format(cal.getTime());
                           	
                             	SQL ="update  "+tablename+" set timestamp= "+
                         		'"'+currentTime+'"'
                         		+" where" 
                         		+" createdAtDate="+'"'+createDate+'"'+" and "
                         		+" createdAtTime="+'"'+createTime+'"'+" and "
                         		+" id="+'"'+id+'"'+" and "
                             	+" userId="+'"'+userId+'"';
                             	System.out.println("Execute SQL: "+SQL);
                             	try
                             	{
                             		Statement update_st = con.createStatement();
                             		int result = update_st.executeUpdate(SQL);
                                    
                                    if (result >0)
                                    {
                                   	System.out.println ("Update Successfully!");
                                    }
                               }
                             catch( SQLException se1 )
                              {
                            	 try{
                      	        	errorFile.write(filename+"\t"+"DB Error:"+se1.getMessage()+"\n");
                                   	errorFile.flush();
                               	   
                                     }
                           	     catch( Exception e1 ) 
                         	      {
                         	         e1.printStackTrace();
                         	       }
                            	 System.out.println("Exec error. error code:"+se1.getErrorCode()+ "message: "+se1.getMessage()); 
                              }
                           	  catch( Exception e ) 
                       	      {
                           		try{
                     	        	errorFile.write(filename+"\t"+"DB Error:"+e.getMessage()+"\n");
                                  	errorFile.flush();
                              	   
                                    }
                          	     catch( Exception e1 ) 
                        	      {
                        	         e1.printStackTrace();
                        	       }
                          	     
                           		  e.printStackTrace();
                       	       }
                          } //if 1062
                        else
                        {
                         		System.out.println("Exec error. error code:"+se.getErrorCode()+ "message: "+se.getMessage());
                         		try{
                     	        	errorFile.write(filename+"\t"+"Exec SQL Error:"+se.getMessage()+"\n");
                                  	errorFile.flush();
                              	   
                                    }
                          	     catch( Exception e1 ) 
                        	      {
                        	         e1.printStackTrace();
                        	       }
                        }
                     }
                 	 catch( Exception e ) 
                 	      {
                 	         e.printStackTrace();
                 	        try{
                 	        	errorFile.write(filename+"\t"+"DB Error:"+e.getMessage()+"\n");
                              	errorFile.flush();
                          	   
                                }
                      	     catch( Exception e1 ) 
                    	      {
                    	         e1.printStackTrace();
                    	       }
                 	       }
            	      }
                        //finished one xml file;
                          try{
                        	  if (numOfDuplicate == 20){
                                	errorFile.write(filename+"\t"+"File Duplicate"+"\n");
                                	errorFile.flush();
                                }
                                else if (numOfDuplicate > 0){
                              	  errorFile.write(filename+"\t"+numOfDuplicate+" Records Duplicate"+"\n");
                                    errorFile.flush();
                                } 
                          }
                    	  catch( Exception e ) 
                  	      {
                  	         e.printStackTrace();
                  	       }
                              
                   }
               
               System.gc() ;     
          	    }
              }
              //finished one sub-dir
              try
        	  {
        	  finishedDirFile.write(dir_children[index]+"\n");
        	  finishedDirFile.flush();
        	  System.out.println("finished sub-directory:"+dir_children[index]);
        	  }
        	  catch( Exception e ) 
      	      {
      	         e.printStackTrace();
      	       }
          }
    	  
        }
      try{
      finishedDirFile.close();}
      catch( Exception e ){
	         e.printStackTrace();}
    
  
  }

  private String replaceInvalidChar (String str)
  {
	  //System. out.println ("str: "+ str);
	  if (str.length() == 0 ) return str;
	  str = str.replaceAll("\\\\", "\\\\\\\\");
	  //System. out.println ("str replaced: "+ str);
	  str = str.replaceAll("\"", "\\\\\"");
	  //System. out.println ("str replaced: "+ str);
	  
	  return str;
  }
  public static void main(String args[]) {
   
	if (args.length < 6)
	{
		System.out.println("Usage: insertdata <Source file directoy> <hostname> <username> <password> <database name> <table name>");
		System.exit(0) ;
	}
	try{  
	String rootDir = args[0];
	String host = args[1];
	String user = args[2];
	String password = args[3];
	String dbname = args[4];
	String tablename = args[5];
	InsertApp insertapp = new InsertApp();
	boolean ret = insertapp.Connect(host,user,password, dbname);
	if (!ret)
	{
		System.out.println("Init Failed!");
		System.exit (0);
	}
    insertapp.insertAllFile(rootDir,tablename);
    insertapp.con.close();
	}
      catch( Exception e ) 
      {
         e.printStackTrace();
       }
      System.out.println("Done!");
  }
  
}
